import numpy
import pymysql
import tushare as ts

def get_stock_basic():
    stock_basic_data = ts.get_stock_basics()

    table_name = 'stock_basic'

    create_table_sql = """
                            CREATE TABLE IF NOT EXISTS {0} (
                            CODE CHAR(6) PRIMARY KEY,
                            NAME TEXT,
                            INDUSTRY TEXT,
                            AREA TEXT,
                            PE FLOAT,
                            OUTSTANDING FLOAT,
                            TOTALS FLOAT,
                            TOTALASSETS FLOAT,
                            LIQUIDASSETS FLOAT,
                            FIXEDASSETS FLOAT,
                            RESERVED FLOAT,
                            RESERVEDPERSHARE FLOAT,
                            ESP FLOAT,
                            BVPS FLOAT,
                            PB FLOAT,
                            TIMETOMARKET DATE,
                            UNDP FLOAT,
                            PERUNDP FLOAT,
                            REV FLOAT,
                            PROFIT FLOAT,
                            GPR FLOAT,
                            NPR FLOAT,
                            HOLDERS INT
                            )
                            """.format(table_name)

    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_basic', charset='utf8')
    cursor = db.cursor()

    cursor.execute(create_table_sql)
    db.commit()

    insert_sql = "INSERT INTO STOCK_BASIC (code,name,industry,area,pe,outstanding,totals,totalassets,liquidassets,fixedassets,reserved,reservedpershare,esp,bvps,pb,timetomarket,undp,perundp,rev,profit,gpr,npr,holders) VALUES "

    for i in range(len(stock_basic_data)):
        data = stock_basic_data.iloc[i]
        code = data.name
        name = data['name']
        industry = data['industry']
        area = data['area']
        pe = data['pe']
        outstanding = data['outstanding']
        totals = data['totals']
        totalassets = data['totalAssets']
        liquidassets = data['liquidAssets']
        fixedassets = data['fixedAssets']
        reserved = data['reserved']
        reservedpershare = data['reservedPerShare']
        esp = data['esp'] if type(data['esp']) == numpy.float64 else 0
        bvps = data['bvps']
        pb = data['pb']
        timetomarket = data['timeToMarket']
        undp = data['undp']
        perundp = data['perundp']
        rev = data['rev']
        profit = data['profit']
        gpr = data['gpr']
        npr = data['npr']
        holders = data['holders']

        insert_data = (
        code, name, industry, area, pe, outstanding, totals, totalassets, liquidassets, fixedassets, reserved,
        reservedpershare, esp, bvps, pb, timetomarket, undp, perundp, rev, profit, gpr, npr, holders)


        insert_sql += str(insert_data) + ','

    print(insert_sql)

    insert_sql_final = insert_sql.rstrip(',')

    cursor.execute(insert_sql_final)
    db.commit()
    db.close()



if __name__ == '__main__':
    get_stock_basic()